package com.maycurobj.repository.oa;

import com.maycurobj.entity.oa.HeTongEntity;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface WorkflowByHeTongRepository extends JpaRepository<HeTongEntity,Integer> {

    /**
     * 合同流程（每刻对公付款）
     * sql 获取 工号对应流程列表 待分页
     * @param employeeId
     * @return
     */
    @Query(nativeQuery = true,value = "select distinct * from ( SELECT DISTINCT biaoti ||'-'|| LIUCHENGBH AS biaoti,LIUCHENGBH,to_date(shenqingrq,'yyyy-mm-dd') as shenqingrq,caozuor,jine,requestid from (SELECT DISTINCT( SELECT wr.requestname FROM WORKFLOW_REQUESTBASE wr WHERE wr.requestid = f47.requestid ) AS biaoti,f47.liuchengbh,f47.shenqingrq,lastname AS caozuor,nvl( caigouze, 0.00 ) AS jine,f47.REQUESTID FROM formtable_main_29 f47,workflow_currentoperator,HrmResource h WHERE workflow_currentoperator.Requestid = f47.requestid AND workflow_currentoperator.userid = h.id AND iscomplete = 1 AND workcode = :employeeId UNION ALL SELECT DISTINCT( SELECT wr.requestname FROM WORKFLOW_REQUESTBASE wr WHERE wr.requestid = f47.requestid ) AS biaoti,f47.liuchengbh,f47.shenqingrq,lastname AS caozuor,nvl( baojiazjh, 0.00 ) AS jine,f47.REQUESTID FROM formtable_main_226 f47,workflow_currentoperator,HrmResource h WHERE workflow_currentoperator.Requestid = f47.requestid AND workflow_currentoperator.userid = h.id AND iscomplete = 1 AND workcode = :employeeId UNION ALL SELECT DISTINCT( SELECT wr.requestname FROM WORKFLOW_REQUESTBASE wr WHERE wr.requestid = f47.requestid ) AS biaoti,f47.liuchengbh,f47.shenqingrq,lastname AS caozuor,nvl( NULL, 0.00 ) AS jine,f47.REQUESTID FROM formtable_main_100 f47,workflow_currentoperator,HrmResource h WHERE workflow_currentoperator.Requestid = f47.requestid AND workflow_currentoperator.userid = h.id AND iscomplete = 1 AND workcode = :employeeId UNION ALL SELECT DISTINCT( SELECT wr.requestname FROM WORKFLOW_REQUESTBASE wr WHERE wr.requestid = f47.requestid ) AS biaoti,f47.liuchengbh,f47.shenqingrq,lastname AS caozuor,nvl( hetongje, 0.00 ) AS jine,f47.REQUESTID FROM formtable_main_273 f47,workflow_currentoperator,HrmResource h WHERE workflow_currentoperator.Requestid = f47.requestid AND workflow_currentoperator.userid = h.id AND (iscomplete = 1 or nodeid = 7912 ) AND workcode = :employeeId UNION ALL SELECT DISTINCT( SELECT wr.requestname FROM WORKFLOW_REQUESTBASE wr WHERE wr.requestid = f47.requestid ) AS biaoti,f47.liuchengbh,f47.shenqingrq,lastname AS caozuor,nvl( hetongje, 0.00 ) AS jine,f47.REQUESTID FROM formtable_main_35 f47,workflow_currentoperator,HrmResource h WHERE workflow_currentoperator.Requestid = f47.requestid AND workflow_currentoperator.userid = h.id AND iscomplete = 1 AND workcode = :employeeId UNION ALL SELECT DISTINCT( SELECT wr.requestname FROM WORKFLOW_REQUESTBASE wr WHERE wr.requestid = f47.requestid ) AS biaoti,f47.liuchengbh,f47.shenqingrq,lastname AS caozuor,nvl( NULL, 0.00 ) AS jine,f47.REQUESTID FROM formtable_main_65 f47,workflow_currentoperator,HrmResource h WHERE workflow_currentoperator.Requestid = f47.requestid AND workflow_currentoperator.userid = h.id AND iscomplete = 1 AND workcode = :employeeId UNION ALL SELECT DISTINCT( SELECT wr.requestname FROM WORKFLOW_REQUESTBASE wr WHERE wr.requestid = f47.requestid ) AS biaoti,f47.liuchengbh,f47.shenqingrq,lastname AS caozuor,nvl( hetongje, 0.00 ) AS jine,f47.REQUESTID FROM formtable_main_35 f47,workflow_currentoperator,HrmResource h WHERE workflow_currentoperator.Requestid = f47.requestid AND workflow_currentoperator.userid = h.id AND iscomplete = 1 AND workcode = :employeeId UNION ALL SELECT DISTINCT( SELECT wr.requestname FROM WORKFLOW_REQUESTBASE wr WHERE wr.requestid = f47.requestid ) AS biaoti,f47.liuchengbh,f47.shenqingrq,lastname AS caozuor,nvl( NULL, 0.00 ) AS jine,f47.REQUESTID FROM formtable_main_17 f47,workflow_currentoperator,HrmResource h WHERE workflow_currentoperator.Requestid = f47.requestid AND workflow_currentoperator.userid = h.id AND workcode = :employeeId UNION ALL SELECT DISTINCT( SELECT wr.requestname FROM WORKFLOW_REQUESTBASE wr WHERE wr.requestid = f47.requestid ) AS biaoti,f47.liuchengbh,f47.shenqingrq,lastname AS caozuor,nvl( NULL, 0.00 ) AS jine,f47.REQUESTID FROM formtable_main_48 f47,workflow_currentoperator,HrmResource h WHERE workflow_currentoperator.Requestid = f47.requestid AND workflow_currentoperator.userid = h.id AND workcode = :employeeId UNION ALL SELECT DISTINCT( SELECT wr.requestname FROM WORKFLOW_REQUESTBASE wr WHERE wr.requestid = f47.requestid ) AS biaoti,f47.liuchengbh,f47.shenqingrq,lastname AS caozuor,nvl( caigouze1, caigouze ) AS jine,f47.REQUESTID FROM formtable_main_287 f47,workflow_currentoperator,HrmResource h WHERE workflow_currentoperator.Requestid = f47.requestid AND workflow_currentoperator.userid = h.id AND workcode = :employeeId UNION ALL SELECT DISTINCT( SELECT wr.requestname FROM WORKFLOW_REQUESTBASE wr WHERE wr.requestid = f47.requestid ) AS biaoti,f47.liuchengbh,f47.shenqingrq,lastname AS caozuor,nvl( fukuanjedxxx, 0.00 ) AS jine,f47.REQUESTID FROM formtable_main_229 f47,workflow_currentoperator,HrmResource h WHERE workflow_currentoperator.Requestid = f47.requestid AND workflow_currentoperator.userid = h.id AND iscomplete = 1 AND workcode = :employeeId UNION ALL SELECT DISTINCT( SELECT wr.requestname FROM WORKFLOW_REQUESTBASE wr WHERE wr.requestid = f47.requestid ) AS biaoti,f47.liuchengbh,f47.shenqingrq,lastname AS caozuor,nvl( shijizfy, 0.00 ) AS jine,f47.REQUESTID FROM formtable_main_68 f47,workflow_currentoperator,HrmResource h WHERE workflow_currentoperator.Requestid = f47.requestid AND workflow_currentoperator.userid = h.id  and nodeid not IN (7947,9347) AND workcode = :employeeId ) ) where biaoti like %:keyWord% ORDER BY requestid DESC ")
    List<HeTongEntity> getWFs(@Param(value = "employeeId") String employeeId,  @Param(value="keyWord") String keyWord ,Pageable pageable);

    /**
     * 获取全部记录
     * @param employeeId
     * @return
     */
    @Query(nativeQuery = true,value = " select DISTINCT count(*) as counts from (SELECT DISTINCT biaoti ||'-'|| LIUCHENGBH AS biaoti,LIUCHENGBH,to_date(shenqingrq,'yyyy-mm-dd') as shenqingrq,caozuor,jine,requestid from (SELECT DISTINCT( SELECT wr.requestname FROM WORKFLOW_REQUESTBASE wr WHERE wr.requestid = f47.requestid ) AS biaoti,f47.liuchengbh,f47.shenqingrq,lastname AS caozuor,nvl( caigouze, 0.00 ) AS jine,f47.REQUESTID FROM formtable_main_29 f47,workflow_currentoperator,HrmResource h WHERE workflow_currentoperator.Requestid = f47.requestid AND workflow_currentoperator.userid = h.id AND iscomplete = 1 AND workcode = :employeeId UNION ALL SELECT DISTINCT( SELECT wr.requestname FROM WORKFLOW_REQUESTBASE wr WHERE wr.requestid = f47.requestid ) AS biaoti,f47.liuchengbh,f47.shenqingrq,lastname AS caozuor,nvl( baojiazjh, 0.00 ) AS jine,f47.REQUESTID FROM formtable_main_226 f47,workflow_currentoperator,HrmResource h WHERE workflow_currentoperator.Requestid = f47.requestid AND workflow_currentoperator.userid = h.id AND iscomplete = 1 AND workcode = :employeeId UNION ALL SELECT DISTINCT( SELECT wr.requestname FROM WORKFLOW_REQUESTBASE wr WHERE wr.requestid = f47.requestid ) AS biaoti,f47.liuchengbh,f47.shenqingrq,lastname AS caozuor,nvl( NULL, 0.00 ) AS jine,f47.REQUESTID FROM formtable_main_100 f47,workflow_currentoperator,HrmResource h WHERE workflow_currentoperator.Requestid = f47.requestid AND workflow_currentoperator.userid = h.id AND iscomplete = 1 AND workcode = :employeeId UNION ALL SELECT DISTINCT( SELECT wr.requestname FROM WORKFLOW_REQUESTBASE wr WHERE wr.requestid = f47.requestid ) AS biaoti,f47.liuchengbh,f47.shenqingrq,lastname AS caozuor,nvl( hetongje, 0.00 ) AS jine,f47.REQUESTID FROM formtable_main_273 f47,workflow_currentoperator,HrmResource h WHERE workflow_currentoperator.Requestid = f47.requestid AND workflow_currentoperator.userid = h.id AND (iscomplete = 1 or nodeid = 7912 ) AND workcode = :employeeId UNION ALL SELECT DISTINCT( SELECT wr.requestname FROM WORKFLOW_REQUESTBASE wr WHERE wr.requestid = f47.requestid ) AS biaoti,f47.liuchengbh,f47.shenqingrq,lastname AS caozuor,nvl( hetongje, 0.00 ) AS jine,f47.REQUESTID FROM formtable_main_35 f47,workflow_currentoperator,HrmResource h WHERE workflow_currentoperator.Requestid = f47.requestid AND workflow_currentoperator.userid = h.id AND iscomplete = 1 AND workcode = :employeeId UNION ALL SELECT DISTINCT( SELECT wr.requestname FROM WORKFLOW_REQUESTBASE wr WHERE wr.requestid = f47.requestid ) AS biaoti,f47.liuchengbh,f47.shenqingrq,lastname AS caozuor,nvl( NULL, 0.00 ) AS jine,f47.REQUESTID FROM formtable_main_65 f47,workflow_currentoperator,HrmResource h WHERE workflow_currentoperator.Requestid = f47.requestid AND workflow_currentoperator.userid = h.id AND iscomplete = 1 AND workcode = :employeeId UNION ALL SELECT DISTINCT( SELECT wr.requestname FROM WORKFLOW_REQUESTBASE wr WHERE wr.requestid = f47.requestid ) AS biaoti,f47.liuchengbh,f47.shenqingrq,lastname AS caozuor,nvl( hetongje, 0.00 ) AS jine,f47.REQUESTID FROM formtable_main_35 f47,workflow_currentoperator,HrmResource h WHERE workflow_currentoperator.Requestid = f47.requestid AND workflow_currentoperator.userid = h.id AND iscomplete = 1 AND workcode = :employeeId UNION ALL SELECT DISTINCT( SELECT wr.requestname FROM WORKFLOW_REQUESTBASE wr WHERE wr.requestid = f47.requestid ) AS biaoti,f47.liuchengbh,f47.shenqingrq,lastname AS caozuor,nvl( NULL, 0.00 ) AS jine,f47.REQUESTID FROM formtable_main_17 f47,workflow_currentoperator,HrmResource h WHERE workflow_currentoperator.Requestid = f47.requestid AND workflow_currentoperator.userid = h.id AND workcode = :employeeId UNION ALL SELECT DISTINCT( SELECT wr.requestname FROM WORKFLOW_REQUESTBASE wr WHERE wr.requestid = f47.requestid ) AS biaoti,f47.liuchengbh,f47.shenqingrq,lastname AS caozuor,nvl( NULL, 0.00 ) AS jine,f47.REQUESTID FROM formtable_main_48 f47,workflow_currentoperator,HrmResource h WHERE workflow_currentoperator.Requestid = f47.requestid AND workflow_currentoperator.userid = h.id AND workcode = :employeeId UNION ALL SELECT DISTINCT( SELECT wr.requestname FROM WORKFLOW_REQUESTBASE wr WHERE wr.requestid = f47.requestid ) AS biaoti,f47.liuchengbh,f47.shenqingrq,lastname AS caozuor,nvl( caigouze1, caigouze ) AS jine,f47.REQUESTID FROM formtable_main_287 f47,workflow_currentoperator,HrmResource h WHERE workflow_currentoperator.Requestid = f47.requestid AND workflow_currentoperator.userid = h.id AND workcode = :employeeId UNION ALL SELECT DISTINCT( SELECT wr.requestname FROM WORKFLOW_REQUESTBASE wr WHERE wr.requestid = f47.requestid ) AS biaoti,f47.liuchengbh,f47.shenqingrq,lastname AS caozuor,nvl( fukuanjedxxx, 0.00 ) AS jine,f47.REQUESTID FROM formtable_main_229 f47,workflow_currentoperator,HrmResource h WHERE workflow_currentoperator.Requestid = f47.requestid AND workflow_currentoperator.userid = h.id AND iscomplete = 1 AND workcode = :employeeId UNION ALL SELECT DISTINCT( SELECT wr.requestname FROM WORKFLOW_REQUESTBASE wr WHERE wr.requestid = f47.requestid ) AS biaoti,f47.liuchengbh,f47.shenqingrq,lastname AS caozuor,nvl( shijizfy, 0.00 ) AS jine,f47.REQUESTID FROM formtable_main_68 f47,workflow_currentoperator,HrmResource h WHERE workflow_currentoperator.Requestid = f47.requestid AND workflow_currentoperator.userid = h.id and nodeid not IN (7947,9347) AND workcode = :employeeId ) ) where biaoti like %:keyWord% ")
    int getCount(@Param(value = "employeeId") String employeeId,@Param(value="keyWord") String keyWord);


}
